package com.xneure.database.define;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map.Entry;
import org.apache.commons.lang3.StringUtils;
import com.xneure.code.bean.DiffCompare;
import com.xneure.code.intf.DiffListen;
import com.xneure.utils.NumUtil;
import com.xneure.utils.ObjectUtil;

public class OracleCreator extends Creator{
	private boolean forced;

	@Override
	public void setForcedUpdate(boolean forced){
		this.forced = forced;
	}

	@Override
	public String create(TableDefine define){
		return getDefine(define);
	}

	@Override
	public boolean create(Connection conn,TableDefine define){
		try{
			Statement createStatement = conn.createStatement();
			TableDefine res = getMetadata(conn,define.getName());
			if(null == res){
				String create = create(define);
				String[] sql = create.split(";\r\n");
				for(String str:sql){
					createStatement.addBatch(str);
				}
				createStatement.executeBatch();
				return true;
			}else if(forced){
				update(conn,define,res);
			}
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}
	}

	public String getTableDefine(String tableName){
		return "SELECT\r\n"
				+ "(select constraint_name from user_constraints con where CON.table_name=utbale.table_name and con.constraint_type = 'P') tableKey,\r\n"
				+ "(SELECT COMMENTS from user_tab_comments com where com.table_name=utbale.table_name AND com.table_type = 'TABLE')tableComment\r\n"
				+ "FROM USER_TABLES utbale \r\n" + "where utbale.table_name ='" + tableName.toUpperCase() + "'";
	}

	public String getColumnDefine(String tableName){
		return "SELECT 	tabCol.COLUMN_NAME colName ,\r\n" + "	(\r\n" + "	SELECT\r\n" + "		count(col.column_name) cKey\r\n" + "	FROM\r\n"
				+ "		user_constraints con,\r\n" + "		user_cons_columns col\r\n" + "	WHERE\r\n" + "		con.constraint_name = col.constraint_name\r\n"
				+ "		AND con.constraint_type = 'P'\r\n" + "		AND col.table_name = tabCol.TABLE_NAME\r\n"
				+ "		AND tabCol.COLUMN_NAME = column_name) isKey,\r\n" + "	tabCol.DATA_TYPE dateType,\r\n" + "	(CASE \r\n"
				+ "		WHEN tabCol.DATA_TYPE ='NUMBER' THEN tabCol.DATA_PRECISION  \r\n"
				+ "		WHEN tabCol.DATA_TYPE like '%CHAR%' THEN tabCol.CHAR_LENGTH\r\n" + "		WHEN tabCol.DATA_TYPE like 'TIMESTAMP%' THEN 0\r\n"
				+ "		ELSE tabCol.DATA_SCALE \r\n" + "	END) dataLength,\r\n" + "	tabCol.DATA_SCALE dataScale,\r\n" + "	tabCol.NULLABLE isNull,\r\n"
				+ "	tabCol.COLUMN_ID id,\r\n" + "	tabCol.data_default defaultValue,\r\n" + "	user_col_comments.comments colComment\r\n" + "FROM\r\n"
				+ "	USER_TAB_COLS tabCol\r\n" + "INNER JOIN user_col_comments ON\r\n" + "	user_col_comments.TABLE_NAME = tabCol.TABLE_NAME\r\n"
				+ "	AND user_col_comments.COLUMN_NAME = tabCol.COLUMN_NAME WHERE tabCol.table_name ='" + tableName + "' or tabCol.table_name ='"
				+ tableName.toUpperCase() + "'";
	}

	@Override
	public String update(TableDefine define,TableDefine dbDefine){
		return updateDefine(dbDefine,define);
	}

	@Override
	public boolean update(Connection conn,TableDefine define){
		return update(conn,define,getMetadata(conn,define.getName()));
	}

	@Override
	public boolean update(Connection conn,TableDefine define,TableDefine dbDefine){
		String def = update(define,dbDefine);
		if(ObjectUtil.isNotNull(def)){
			String[] sql = def.split(";\r\n");
			Statement createStatement;
			try{
				createStatement = conn.createStatement();
				for(String str:sql){
					createStatement.addBatch(str);
				}
				createStatement.executeBatch();
				return true;
			}catch(SQLException e){
				e.printStackTrace();
				return false;
			}
		}
		return false;
	}

	private String getDefine(TableDefine define){
		StringBuilder tableDefine = new StringBuilder("CREATE TABLE ");
		StringBuilder comment = new StringBuilder();
		StringBuilder pk = new StringBuilder("ALTER TABLE ");
		pk.append(define.getName());
		pk.append(" ADD PRIMARY KEY (");
		tableDefine.append(define.getName());
		tableDefine.append("(\r\n");
		buildComment(define.getName(),define.getComment(),comment);
		for(ColumnDefine col:define.getColumns()){
			tableDefine.append(buildDefine(null,col));
			tableDefine.append(",\r\n");
			comment.append(buildComment(define.getName(),col));
			if(col.isPrimaryKey()){
				pk.append(col.getName());
				pk.append(",");
			}
		}
		if(pk.indexOf(",") > -1){
			pk.setLength(pk.length() - 1);
			pk.append(");\r\n");
		}else{
			pk.setLength(0);
		}
		tableDefine.setLength(tableDefine.length() - 3);
		tableDefine.append("\r\n);\r\n");
		return tableDefine.append(comment).append(pk).toString();
	}

	private String updateDefine(TableDefine dbDefine,TableDefine newDefine){
		StringBuilder updateDefine = new StringBuilder("ALTER TABLE ");
		StringBuilder priority = new StringBuilder();
		StringBuilder comment = new StringBuilder();
		StringBuilder pk = new StringBuilder("ALTER TABLE ");
		pk.append(dbDefine.getName());
		pk.append(" ADD PRIMARY KEY (");
		TableDefineImpl _newDef = (TableDefineImpl)newDefine;
		TableDefineImpl dbDef = (TableDefineImpl)dbDefine;
		updateDefine.append(dbDefine.getName());
		updateDefine.append("\r\n");
		if(StringUtils.equalsIgnoreCase(dbDefine.getComment(),newDefine.getComment()) == false){
			buildComment(newDefine.getName(),newDefine.getComment(),comment);
		}
		for(Entry<String,ColumnDefine> ent:_newDef.columns.entrySet()){
			ColumnDefine tmp = dbDef.columns.remove(ent.getKey());
			ColumnDefine col = ent.getValue();
			if(null == col){
				priority.append("ALTER TABLE ");
				priority.append(dbDefine.getName());
				priority.append(" DROP(\"");
				priority.append(tmp.getName());
				priority.append("\");\r\n");
			}else{
				if(tmp == null){
					updateDefine.append("ADD ( ");
					updateDefine.append(buildDefine(null,col));
					updateDefine.append("  )\r\n");
				}else{
					updateDefine.append(updateTableDefine(dbDefine.getName(),tmp,col));
				}
				if(null == tmp || (col.isPrimaryKey() && tmp.isPrimaryKey() != col.isPrimaryKey())){
					pk.append(col.getName());
					pk.append(",");
				}
				if(null == tmp || StringUtils.equalsIgnoreCase(tmp.getComment(),col.getComment()) == false){
					comment.append(buildComment(newDefine.getName(),col));
				}
			}
		}
		if(dbDef.columns.size() > 0){
			for(Entry<String,ColumnDefine> ent:dbDef.columns.entrySet()){
				priority.append("ALTER TABLE ");
				priority.append(dbDefine.getName());
				priority.append(" DROP(\"");
				priority.append(ent.getValue().getName());
				priority.append("\");\r\n");
			}
		}
		if(pk.indexOf(",") > -1){
			if(ObjectUtil.isNotNull(dbDef.getConstraint())){
				priority.append("ALTER TABLE ");
				priority.append(dbDefine.getName());
				priority.append(" DROP CONSTRAINT ");
				priority.append(dbDef.getConstraint());
				priority.append(";\r\n");
			}
			pk.setLength(pk.length() - 1);
			pk.append(");\r\n");
		}else{
			pk.setLength(0);
		}
		if(updateDefine.indexOf("(") > -1){
			updateDefine.append(";\r\n");
		}else{
			updateDefine.setLength(0);
		}
		return priority.append(updateDefine).append(comment).append(pk).toString();
	}

	private void buildComment(String tName,String dComment,StringBuilder comment){
		if(ObjectUtil.isNotNull(dComment)){
			comment.append("COMMENT ON TABLE ");
			comment.append(tName);
			comment.append(" IS '");
			comment.append(dComment);
			comment.append("';\r\n");
		}
	}

	protected String buildComment(String tbName,ColumnDefine col){
		if(ObjectUtil.isNotNull(col.getComment())){
			StringBuilder comment = new StringBuilder();
			comment.append("COMMENT ON COLUMN ");
			comment.append(tbName);
			comment.append(".");
			comment.append(col.getName());
			comment.append(" IS '");
			comment.append(col.getComment());
			comment.append("';\r\n");
			return comment.toString();
		}
		return "";
	}

	protected String buildDefine(ColumnDefine refer,ColumnDefine col){
		StringBuilder sql = new StringBuilder();
		sql.append(col.getName());
		sql.append(" ");
		sql.append(col.getType().toUpperCase());
		switch(col.getType().toUpperCase()){
			case "NVARCHAR2":
				if(null != refer && refer.getLength() == col.getLength()){
					break;
				}
				if(col.getLength() > 0){
					sql.append("(").append(col.getLength()).append(")");
				}else{
					sql.append("(255)");
				}
				break;
			case "TIMESTAMP":
				if(null != refer && refer.getLength() == col.getLength()){
					break;
				}
				if(col.getLength() > 9){
					throw new RuntimeException("超出最大长度范围9：".concat(col.getName()));
				}
				sql.append("(").append(col.getLength()).append(")");
				break;
			case "NUMBER":
				if(null != refer && refer.getLength() == col.getLength()){
					break;
				}
				if(col.getLength() > 0){
					sql.append("(").append(col.getLength());
					if(col.getPrecision() > 0){
						if(null == refer || refer.getPrecision() != col.getPrecision()){
							sql.append(",").append(col.getPrecision());
						}
					}
					sql.append(")");
				}else if(col.getPrecision() > 0){
					if(null != refer && refer.getPrecision() == col.getPrecision()){
						break;
					}
					sql.append("(0");
					sql.append(",").append(col.getPrecision());
					sql.append(")");
				}
				break;
			default:
				break;
		}
		if(ObjectUtil.isNotNull(col.getDefaultValue())){
			if(NumUtil.isNum(col.getDefaultValue())){
				sql.append(" DEFAULT ");
				sql.append(col.getDefaultValue());
			}else{
				sql.append(" DEFAULT '");
				sql.append(col.getDefaultValue());
				sql.append("'");
			}
		}
		if(col.isNullAble()){
			sql.append(" NOT NULL");
		}else{
			sql.append(" NULL");
		}
		return sql.toString();
	}

	private String updateTableDefine(String tableName,ColumnDefine dbCol,ColumnDefine newDefine){
		StringBuilder sb = new StringBuilder();
		ColumnDefineImpl newDef = (ColumnDefineImpl)newDefine;
		DiffCompare dc = new DiffCompare();
		dc.setIgnoreCase(true);
		dc.setListen(new DiffListen(){
			private boolean isEnd = false;

			@Override
			public void currObject(Object srcObj,Object tagObj){
			}

			@Override
			public void Handle(List<String> path,Object src,Object target){
				sb.append("MODIFY ( ");
				sb.append(buildDefine(dbCol,newDefine));
				sb.append(" )\r\n");
				isEnd = true;
			}

			@Override
			public boolean isIgnore(List<String> path){
				return path.contains("primaryKey") || path.contains("comment") || path.contains("id");
			}

			@Override
			public boolean isEnd(){
				return isEnd;
			}
		});
		dc.compare(dbCol,newDef);
		return sb.toString();
	}
}
